# coding:utf8
# TODO datetime(6)
# 外键约束 https://www.cnblogs.com/JiangLe/p/5812613.html

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

import os
import re


from settings import *


BASEFILE = "models.py"
engine = create_engine(DATABASE_URL)
Session = sessionmaker(bind=engine)

session = Session()

with open(BASEFILE, "w") as f:
    f.write("from sqlalchemy.ext.declarative import declarative_base\n"
            "from sqlalchemy import Column, ForeignKey\n"
            "from sqlalchemy.types import Integer, String, DateTime\n"
            "from sqlalchemy.dialects.mysql import LONGTEXT, DATETIME, DATE, TINYINT, CHAR, SMALLINT\n"
            "Base = declarative_base()\n\n")


class Column(object):

    def __init__(self, *args, **kwargs):
        self.name = args[0] # column name
        self.type = args[1] # column type
        self.nullable = args[2] # column nullable
        self.key = args[3] # column key
        self.default = args[4] # column default
        self.foreignkeys = kwargs

    def get_length(self):
        return re.search("(\d+)", self.type).groups()[0]

    def get_type(self):
        if self.type.startswith("int"):
            return "Integer"
        if self.type.startswith("varchar"):
            return "String({0})".format(self.get_length())
        if self.type.startswith("char"):
            return "CHAR({0})".format(self.get_length())
        if self.type.startswith("longtext"):
            return "LONGTEXT"
        if self.type.startswith("datetime"):
            return "DATETIME(6)"
        if self.type.startswith("tinyint"):
            return "TINYINT({0})".format(self.get_length())
        if self.type.startswith("smallint"):
            return "SMALLINT({0})".format(self.get_length())
        print(self.type)
        raise Exception

    def get_key(self):
        if self.key == "PRI":
            return ", primary_key=True"
        elif self.key == "MUL":
            return ""
            # TODO
        elif self.key ==  "UNI":
            return ", unique=True"
        elif not self.key:
            return ""
        else:
            raise Exception

    def get_null(self):
        if self.nullable == "NO":
            return ", nullable=False"
        elif self.nullable == "YES":
            return ""
        else:
            raise Exception

    def get_default(self):
        if self.default == None:
            return ""
        else:
            raise Exception

    def get_foreign(self):
        if self.name in self.foreignkeys:
            return ", ForeignKey('{0}')".format(self.foreignkeys[self.name])
        else:
            return ""

    def __repr__(self):
        data = "{0} = Column({1}".format(self.name, self.get_type())
        data += self.get_foreign()
        data += self.get_key()
        data += self.get_null()
        data += self.get_default()
        data += ")"
        return data


class Model(object):

    def __init__(self, table_name):
        self.table_name = table_name
        self.columns = []
        self.foreignkeys = {}
        for foreign in session.execute("select table_name,column_name,referenced_table_name,"
                                      "referenced_column_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE "
                                      "where constraint_schema=\"{0}\" and table_name=\"{1}\";".format(DATABASE, self.table_name)):
            if foreign[3] == None:
                pass # 主键约束
            else:
                self.foreignkeys[foreign[1]] = "{0}.{1}".format(foreign[2], foreign[3])

    def sync(self):
        with open(BASEFILE, "a") as f:
            f.write("\n\nclass {0}(Base):\n".format(self.table_name))
            f.write("    __tablename__ = \"{0}\"\n".format(self.table_name))
            f.write("    __table_args__ = { \"mysql_charset\" : \"utf8\"}\n\n")


            for column in self.columns:
                f.write("    {0}\n".format(str(column)))

    def add_column(self, column):
        self.columns.append(Column(*column, **self.foreignkeys))



results = session.execute("show tables")
tables = map(lambda x: x[0], session.execute("show tables"))
models = []


for table in tables:
    model = Model(table)
    for desc in  session.execute("desc {0}".format(table)):
        model.add_column(desc)
    model.sync()
    models.append(model)
